﻿Imports Microsoft.VisualBasic
Imports System.Data

<System.ComponentModel.DataObjectAttribute(True)> _
Public Class BLL_SearchEnlistRecord

    Public Sub New()
        ' 
        ' TODO: 在此加入建構函式的程式碼 
        ' 
    End Sub

    'SELECT 
    <System.ComponentModel.DataObjectMethod(System.ComponentModel.DataObjectMethodType.[Select])> _
    Public Function BLL_Select(ByVal CreateBy As String, ByVal trainingWHour As String) As DataTable

        Dim sql As New MyStringBuilder
        Dim dat As New clsData

        sql.AppendFormat("SELECT A.ApplicationId,a.status,a.VolunteerID,e.VolunteerName,b.*, c.Text,d.OrganizationName, CourseCount = isnull(etr.CourseCount,0) ")
        sql.AppendFormat(",oo.VOrganizationName  ")
        sql.AppendFormat("FROM EnlistRecordApplication AS a ")
        sql.AppendFormat("left join (select EnlistID, VolunteerID, CourseCount = count(*) from EnlistTrainingRecord etr inner join EnlistTrainingCourse etc on etr.CourseID = etc.CourseID group by EnlistID, VolunteerID) etr on a.EnlistID = etr.EnlistID and a.VolunteerID = etr.VolunteerID and a.Status = 'AP' ")
        sql.AppendFormat("inner join VolunteerStat vs on a.VolunteerID = vs.VolunteerID ")
        sql.AppendFormat("INNER JOIN EnlistHeader AS b ON a.EnlistID = b.EnlistID ")
        sql.AppendFormat("INNER JOIN CodeMapping AS c ON a.Status = c.Value and c.tablename='Form' ")
        sql.AppendFormat("INNER JOIN organizationview d on b.UOrganizationID=d.UOrganizationID ")
        sql.AppendFormat("INNER JOIN VolunteerDistinctView e on A.VolunteerId=e.VolunteerId ")

        sql.AppendFormat(" left JOIN (select vm.VolunteerID,vm.VOrganizationID,vo.VOrganizationName,vo.DirectUOrganizationID from VOrganizationMapping vm ")
        sql.AppendFormat("inner join VOrganizationViewV2 vo on vm.VOrganizationID=vo.VOrganizationID ) oo on a.VolunteerID=oo.VolunteerID  ")


        sql.AppendFormat("WHERE a.Status NOT IN ('RW') ")
        sql.AppendFormat("AND a.CreateBy = '{0}' ", CreateBy)

        If trainingWHour <> "" Then
            sql.AppendFormat("and vs.TrainingWHour <= {0} ", trainingWHour)
        End If
        
        sql.AppendFormat("Order by a.EnlistId desc ")

        Return dat.GetData(sql)

    End Function

    'Update 
    <System.ComponentModel.DataObjectMethod(System.ComponentModel.DataObjectMethodType.Update)> _
      Public Function BLL_Update(ByVal original_ApplicationID As Integer) As Integer

        Dim dat As New clsData
        Dim sql As New MyStringBuilder

        Using scope As New Transactions.TransactionScope
            sql.Length = 0
            sql.AppendFormat("delete er from EnlistRecord er inner join EnlistRecordApplication era on er.EnlistID = era.EnlistID and er.VolunteerID = era.VolunteerID where ApplicationID={0} ", original_ApplicationID)

            dat.AccData(sql)
            sql.Length = 0

            sql.AppendFormat("Update EnlistRecordApplication set Status='RW' where ApplicationID={0} ", original_ApplicationID)

            dat.AccData(sql)
            scope.Complete()

            Return 1

        End Using

    End Function

End Class
